﻿Imports DTO
Imports System.Data.OleDb

Public Class NguoiDungDAO

    Public Function LayDanhSach() As List(Of NguoiDungDTO)
        Dim strSQL = "Select * From NGUOIDUNG"
        Dim conn As OleDbConnection = DataProvider.ConnectDB
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader

        Dim ds As New List(Of NguoiDungDTO)
        While reader.Read()
            Dim ndDTO As New NguoiDungDTO
            ndDTO.TenDangNhap = reader("TenDangNhap")
            ndDTO.MatKhau = reader("MatKhau")
            ndDTO.MaLoaiNguoiDung = reader("MaLoaiNguoiDung")
            ds.Add(ndDTO)
        End While
        conn.Close()

        Return ds
    End Function


    Public Function LayBang() As DataTable
        Dim strSQL = "Select * From NGUOIDUNG"
        Dim conn As OleDbConnection = DataProvider.ConnectDB
        Dim dt As New DataTable
        Dim dp As New OleDbDataAdapter(strSQL, conn)
        dp.Fill(dt)
        conn.Close()
        Return dt
    End Function


    Public Function DangNhap(ByRef ndDTO As NguoiDungDTO) As Integer

        Dim strSQL As String = " SELECT NGUOIDUNG.TenDangNhap, NGUOIDUNG.MatKhau, LOAINGUOIDUNG.DanhSach, LOAINGUOIDUNG.PhatSinh, LOAINGUOIDUNG.BaoBieu, LOAINGUOIDUNG.TraCuu, LOAINGUOIDUNG.BangDiem, LOAINGUOIDUNG.QuanTri "
        strSQL = strSQL + " FROM LOAINGUOIDUNG INNER JOIN NGUOIDUNG ON LOAINGUOIDUNG.MaLoaiNguoiDung = NGUOIDUNG.MaLoaiNguoiDung "
        strSQL = strSQL + " WHERE NGUOIDUNG.TenDangNhap='" + ndDTO.TenDangNhap + "' AND NGUOIDUNG.MatKhau='" + ndDTO.MatKhau + "'"
        Dim conn As New OleDbConnection()
        conn = DataProvider.ConnectDB()
        Dim adapter As New OleDbDataAdapter(strSQL, conn)
        Dim dt As New DataTable
        adapter.Fill(dt)
        conn.Close()

        If (dt.Rows.Count = 1) Then
            'Đăng nhập thành công
            'Lưu trữ biến boolean để thể hiện ẩn/hiện các menu
            ndDTO.DanhSach = dt.Rows(0)("DanhSach")
            ndDTO.PhatSinh = dt.Rows(0)("PhatSinh")
            ndDTO.BaoBieu = dt.Rows(0)("BaoBieu")
            ndDTO.TraCuu = dt.Rows(0)("TraCuu")
            ndDTO.BangDiem = dt.Rows(0)("BangDiem")
            ndDTO.QuanTri = dt.Rows(0)("QuanTri")
            Return 1
        Else
            Return 0
        End If

    End Function


    Public Function DoiMatKhau(ByVal ndDTO As NguoiDungDTO) As Integer
        Dim strSQL As String = "UPDATE NGUOIDUNG SET MatKhau='" + ndDTO.MatKhau + "' WHERE TenDangNhap='" + ndDTO.TenDangNhap + "'"
        Dim conn As New OleDbConnection()
        conn = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()

        If r = 1 Then
            Return 1
        Else
            Return 0
        End If
    End Function


    Public Function ThemNguoiDung(ByVal ndDTO As NguoiDungDTO) As Integer
        Dim strSQL As String = "SELECT COUNT(*) FROM NGUOIDUNG WHERE TenDangNhap='" + ndDTO.TenDangNhap + "'"
        Dim conn As OleDbConnection = DataProvider.ConnectDB
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteScalar()
        conn.Close()
        If r = 1 Then
            Return -1 ''Ten dang nhap da ton tai
        Else
            'Neu ten dang nhap chua co nguoi dang ky thi tien hanh dang ky
            strSQL = "INSERT INTO NGUOIDUNG(TenDangNhap,MatKhau,MaLoaiNguoiDung) VALUES('" + ndDTO.TenDangNhap + "','" + ndDTO.MatKhau + "'," + ndDTO.MaLoaiNguoiDung.ToString + ")"
            conn = DataProvider.ConnectDB
            cmd = New OleDbCommand(strSQL, conn)
            r = cmd.ExecuteNonQuery()
            conn.Close()
            If r = 1 Then
                Return 1 'Dang ky thanh cong
            Else
                Return 0 'ko thanh cong
            End If
        End If

    End Function



    Public Function XoaNguoiDung(ByVal ndDTO As NguoiDungDTO) As Integer
        Dim strSQL As String = " DELETE FROM NGUOIDUNG WHERE TenDangNhap='" + ndDTO.TenDangNhap + "'"
        Dim conn As OleDbConnection = DataProvider.ConnectDB
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery
        conn.Close()

        If r = 1 Then
            Return 1
        Else
            Return 0
        End If
    End Function


    Public Function CapNhatNguoiDung(ByVal ndDTO As NguoiDungDTO) As Integer
        Dim strSQL As String = " UPDATE NGUOIDUNG SET MaLoaiNguoiDung=" + ndDTO.MaLoaiNguoiDung.ToString() + " WHERE TenDangNhap='" + ndDTO.TenDangNhap + "'"
        Dim conn As OleDbConnection = DataProvider.ConnectDB
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery
        conn.Close()


        If r = 1 Then
            Return 1
        Else
            Return 0
        End If
    End Function
End Class

